#include "clientDataProcess.h"
#include "macro.h"
#include "getrequestprocess.h"
#include <QtSql>
#include <QTimer>

ClientDataProcess::ClientDataProcess(QWidget *parent):
    QObject(parent)
{
    qury=NULL;
    getReqProcess=NULL;
    timerForDBConnect=NULL;

}


void ClientDataProcess::slotConnectDB()
{
    timerForDBConnect=new QTimer(this);
    timerForSQL=new QTimer(this);
    connect(timerForDBConnect,SIGNAL(timeout()),this,SLOT(slotTimerEventDBConnect()));
    connect(timerForSQL,SIGNAL(timeout()),this,SLOT(eventOfSQLTimer()));

    if(!createConnection(DB_NAME,DB_HOST,DB_USERNAME,
           DB_PWD,QString(DB_Con_Name_ClientDataProcess)))
    {
        emit sigErrorInfo("Connection "DB_Con_Name_ClientDataProcess" failed.\n"
                          +tr("数据库连接失败，程序无法正常运行。请重启程序"));
        exit(10);
    }
    else
        emit sigErrorInfo("connection "DB_Con_Name_ClientDataProcess" success");

}
/***************************************************
在ThreadDBORelated的RUN函数中，初始化DBO类后，会立即调用createConnection函数
建立与数据库的连接.
获得该连接的方法：
QSqlDatabase db=QSqlDatabase::database( QString connectionName);
相应的，要用下面的语句来定义一个QSqlQuery。
QSqlQuery query(QSqlDatabase::database( QString connectionName));
******************************************************/
bool ClientDataProcess::createConnection(const QString dataBase, const QString host, const QString user,
               const QString passWord, const QString connectionName) //建立一个连接
{
     //plugin的搜索路径增加当前目录下的plugins文件夹。该语句主要为了发布之便。
    //qApp->addLibraryPath(qApp->applicationDirPath () + "/plugins");

    dboConnectionName=connectionName;
    //添加驱动
    QSqlDatabase connectionFromServer=QSqlDatabase::addDatabase(DB_TYPE,connectionName);
    connectionFromServer.setHostName( host);//数据库所在主机的IP
    connectionFromServer.setDatabaseName(dataBase);//操作example数据库
    if(!connectionFromServer.open(user,passWord))
    {
        emit sigErrorInfo("Database Error"+connectionFromServer.lastError().text());
        return false;
    }
    else
    {
        timerForDBConnect->start(MSForDBConnect);

         timerForSQL->start(MsForEventSQLTimer); //大约1000ms调用一次.主要每隔一定时间（interval）给cilent返回结果，及超时处理（numForTimer）。

        qury=new QSqlQuery(QSqlDatabase::database(connectionName));
        qury->setForwardOnly(true);
        getReqProcess=new GetRequestProcess(qury,this);
    }

    return true;
}

void ClientDataProcess::slotTimerEventDBConnect()
{
    QSqlQuery tempQuery(QSqlDatabase::database(dboConnectionName));

    tempQuery.exec("select 1");
}

void ClientDataProcess::slotProcessClientData(QByteArray appData, QByteArray hostIP, quint16 port)
{
    appData=appData.simplified();

    QByteArray result;
    if(appData.toUpper().startsWith("GET"))
        result=getReqProcess->analyzeGetDatagram(appData);
    else if(appData.endsWith(";"))  //以分号结尾，即是普通的SQL语句
       result=analyzeNrmSQLDatagram(appData);
    else if(!appData.toUpper().startsWith("MAC")) //实时数据请求
        result=analyzeRealTimeSQLDatagram(appData,hostIP,port);

    emit sigClientDataProcessDone(result,hostIP,port);
}

QByteArray ClientDataProcess::analyzeNrmSQLDatagram(QByteArray &datagram)
{
    quint8 columnCount;//查询结果的列数
    int n,row(0);
    QByteArray reply,receivedSQL;
    QList <QByteArray> temp=datagram.simplified().split(';');
    if(temp.size()==2)
        receivedSQL=temp.at(0);
    else if(temp.size()==3&&(temp.at(0).contains("UTF-8")||temp.at(0).contains("RAW")))
        receivedSQL=temp.at(1);
    else return "ERROR:Syntax ERROR";

    if(receivedSQL.simplified().toUpper().startsWith("SELECT")&&!receivedSQL.toUpper().contains("LIMIT"))
        receivedSQL.append(" limit "+QByteArray::number(maxRowCount));

    QByteArray upperSQL=receivedSQL.simplified().toUpper();
   /*
    if(!upperSQL.contains("SELECT")&&!upperSQL.contains("UPDATE")&&!upperSQL.contains("DELETE")&&
            !upperSQL.contains("TRUNCATE")&&!upperSQL.contains("SHOW")&&!upperSQL.contains("INSERT")
            &&!upperSQL.contains("DESCRIBE"))
        return "ERROR: Syntax ERROR of SQL";
        */
    QSqlQuery tempQuery(QSqlDatabase::database(dboConnectionName));

    if(tempQuery.exec(receivedSQL))  //如果数据库操作成功
    {
           columnCount=tempQuery.record().count();
           while(tempQuery.next()&&row<maxRowCount)
           {
               row++;
                for( n=0;; n++)
               {
                    if(temp.at(0).contains("UTF-8"))
                        reply.append(tempQuery.value(n).toString().toUtf8());
                    else if(temp.at(0).contains("RAW"))
                        reply.append(tempQuery.value(n).toByteArray());
                    else
                        reply.append(tempQuery.value(n).toString().toUtf8()); //统一UTF8编码

                    if(n<columnCount-1)
                        reply.append('\t');
                    else break;
               }
                reply.append('\n');
                if(reply.size()>=UDP_MAX_Bytes)
                    break;
           }//while结束
            if(row==maxRowCount)
                reply.append(QByteArray::number(tempQuery.size()-maxRowCount+1));
            if(temp.at(0).contains("UTF-8"))
                reply.prepend("UTF-8\n");
            else if(temp.at(0).contains("RAW"))
                reply.prepend("RAW\n");
        }   // if(qury->exec(datagram))  结束
    else //如果操作数据库有误
    {
        reply.append("ERROR: DATABASE ERROR.");
        reply.append(tempQuery.lastError().text().toLatin1());
    }
    if(reply.size()<=UDP_MAX_Bytes)
        return reply;
    else
        return reply.mid(0,UDP_MAX_Bytes);
}

QByteArray ClientDataProcess::analyzeRealTimeSQLDatagram(QByteArray &datagram, QByteArray clientIP, quint16 clientPort)
{
    QSqlQuery tempQuery(QSqlDatabase::database(dboConnectionName));

    bool ok;
    QByteArray reply;
    QList<QByteArray> list=datagram.simplified().split(';');

    if(list.size()!=2||(list.size()!=3&&(datagram.toUpper().startsWith("UTF-8")||datagram.toUpper().startsWith("RAW"))))
    {
        reply.append("ERROR: SYNTAX ERROR. This packet doesn't start with 'Mac',"
                     "and is supposed to be a SQL. But you have an error in your syntax.It's caused by improper use of ';'");
        return reply;  //非法语句则跳出
    }

   QByteArray SQLStr;
    if(datagram.startsWith("UTF-8"))
    {
        SQLStr=list.at(1);
        SQLStr.prepend(";");
        SQLStr.prepend("UTF-8");
        SQLStr.append(";");
        list.removeFirst();
    }
    else if(datagram.startsWith("RAW"))
    {
        SQLStr=list.at(1);
        SQLStr.prepend(";");
        SQLStr.prepend("RAW");
        SQLStr.append(";");
        list.removeFirst();
    }
    else
    {
        SQLStr=list.at(0);
        SQLStr.append(";");
    }
    QByteArray upperSQL=SQLStr.toUpper();
    if(!upperSQL.contains("SELECT")&&!upperSQL.contains("UPDATE")&&!upperSQL.contains("DELETE")&&
            !upperSQL.contains("TRUNCATE")&&!upperSQL.contains("SHOW"))
        return "ERROR: Syntax ERROR of SQL";


    quint32 interval=list.at(1).toULong(&ok,10);

    if(interval>=1000)  //实时数据请求的处理，主要是在AliveCmdRec表中注册该命令
    {
            tempQuery.exec("delete from AliveCmdRec where "
                     "CmdType='SQL'   and trim(SourceIP)='"+clientIP+"'"
                     " and SourcePort="+QString::number(clientPort,10) ); //先清除可能已有的记录
            /*****************************插入命令记录表*********************/
            if(!tempQuery.exec("insert into AliveCmdRec(CmdType,CmdSubType,"
                 "CmdParam,NumForTimer,SourceIP,SourcePort) "
                 "values('SQL', \" "  +SQLStr+" \" ,"+QString::number(interval/1000,10)+","
                 "0, '"+clientIP+"' ,"+QString::number(clientPort,10)+")"
                 ) )
            {
                reply.append("ERROR: DATABASE ERROR.");
                reply.append(tempQuery.lastError().text().toLatin1());
                return reply;  //非法语句则跳出
            }
            else
                reply.append("INFO: Request Processed");
             /*****************************插入命令记录表结束*********************/
    }   // if(interval>=1000) 结束

    else if(interval>1) // 非法的interval值
    {
        reply.append("ERROR: PARAMETER ERROR. interval value not supported!");
        return reply;
    }

    else if(interval==1)  //继续当前发送实时数据状态
    {
        quint32 commandParameter;// 即Interval/1000
        quint16 numberForTimer;
        bool ok;
        tempQuery.exec("select  NumForTimer, CmdParam from AliveCmdRec"
                 " where CmdType='SQL'  and SourceIP='"+clientIP+
                 "'   and SourcePort=" +QString::number(clientPort,10) );
          if(tempQuery.next())
            {
               commandParameter=tempQuery.value(1).toUInt(&ok);
                numberForTimer=tempQuery.value(0).toUInt(&ok);

                numberForTimer=numberForTimer%commandParameter; //计数器清零

                QSqlQuery tempQuery2(QSqlDatabase::database(dboConnectionName));

                tempQuery2.exec("update AliveCmdRec set NumForTimer="+
                     QString::number(numberForTimer,10)+" where CmdType='SQL' "
                     " and SourceIP='"+clientIP+"'   and SourcePort=" +
                     QString::number(clientPort,10));

                reply.append("INFORMATION: realTime Continue Request accepted");
                return reply;
              } //  if(qury->next())结束
          else
              reply.append("ERROR: No realtime is on.");
    }

    else if(interval==0) //主动结束当前发送实时数据状态
    {
        tempQuery.exec("delete from AliveCmdRec where "
                 "CmdType='SQL'   and SourceIP='"+clientIP+"'"
                 " and SourcePort="+QString::number(clientPort,10) );
        reply.append("INFO: realTime Cancel Request accepted");
        return reply;
    }
    return reply;
}

/********每1000ms调用一次。目前主要是为实时数据发送服务**********/
void ClientDataProcess::eventOfSQLTimer()
{
    QSqlQuery tempQuery(QSqlDatabase::database(dboConnectionName));

    QByteArray replyInfo, strCmd;
    bool ok;

    QList <QString> listCmdSubType;
    QList <quint32> listCmdParm;//  参数即Interval/1000
    QList <quint16> listNumberForTimer;
    QList <quint16> listSourcePort;
    QList <QString> listSourceIP;

    if(!tempQuery.exec("select  CmdSubType , CmdParam ,NumForTimer,"
             "SourceIP,  SourcePort from AliveCmdRec where Cmdtype='SQL'"))
    {
        emit sigErrorInfo("ERROR:Fail in eventOfSQLTimer."+tempQuery.lastError().text());
        return;
    }
    while(tempQuery.next())
    {
        listCmdSubType.append(tempQuery.value(0).toString());
        listCmdParm.append(tempQuery.value(1).toUInt(&ok));
        listNumberForTimer.append(tempQuery.value(2).toInt(&ok));
        listSourceIP.append(tempQuery.value(3).toString());
        listSourcePort.append(tempQuery.value(4).toInt(&ok));
    }
    for(int n=0;n<listCmdSubType.size();n++)
    {
        if(listNumberForTimer.at(n)%listCmdParm.at(n)==0) //需要发送实时数据
         {
            replyInfo.append("RealTimeElapsed\t");//提示是实时数据请求的返回
            replyInfo.append(QString::number(listNumberForTimer.at(n)*1000));//已经过去的时间
            replyInfo.append('\n');

            strCmd.append(listCmdSubType.at(n).toLatin1());

            replyInfo.append(analyzeNrmSQLDatagram(strCmd));
            emit sigClientDataProcessDone(replyInfo,listSourceIP.at(n).toUtf8(),listSourcePort.at(n));

            strCmd.clear();replyInfo.clear();
             if(!tempQuery.isActive())  //如果实时请求命令中的SQL语句有错，则清除该实时命令
            {
                tempQuery.exec("delete from AliveCmdRec where "
                         "CmdType='SQL'   and trim(SourceIP)='"+listSourceIP.at(n)+"'"
                         " and SourcePort="+QString::number(listSourcePort.at(n),10) ); //清除记录
            }
        }

         if(listNumberForTimer.at(n)>=maxTimesOfRealTime) //如果连接超时
        {
             tempQuery.exec("delete from AliveCmdRec where CmdType='SQL' "
                      " and SourceIP='"+listSourceIP.at(n)+"'   and SourcePort=" +
                      QString::number(listSourcePort.at(n),10));
         }
         else  //如果连接未超时,则更新表中的NumForTimer
         {
             tempQuery.exec("update AliveCmdRec set NumForTimer="+
                  QString::number(listNumberForTimer.at(n)+1,10)+" where CmdType='SQL' "
                  " and SourceIP='"+listSourceIP.at(n)+"'   and SourcePort=" +
                  QString::number(listSourcePort.at(n),10));
         }

    }
}

ClientDataProcess::~ClientDataProcess()
{
    QSqlDatabase db=QSqlDatabase::database(dboConnectionName);
    if(db.isOpen())
        db.close();

}

